Release 10.1A: OpenEdge Data Management:
SQL Reference

CREATE SEQUENCE

A sequence is an object for creating an incremental number series. Sequences can generate sequential values within any integer range with either positive or negative increments. The database holds the sequence definition and keeps track of the next available value.

Use the following syntax to generate a sequence:

SYNTAX

CREATE SEQUENCE [schema_name.]sequence_name      [INCREMENT BY value],      [START WITH value],      [MAXVALUE value | NOMAXVALUE],      [MINVALUE value | NOMINVALUE],      [CYLCE | NOCYLCE]

schema_name

Specifies the schema to contain the sequence. If schema_name is not specified, the sequence generator creates the sequence in the current schema.

Note: Currently, OpenEdge supports only the PUBLIC (PUB) schema.

sequence_name

Specifies the name of the sequence to be created.

INCREMENT BY

Specifies the interval between sequence numbers. The value can be any positive or negative integer, but cannot be 0. When the value is positive, the sequence ascends. If it is negative, the sequence descends. The default value is 1.

START WITH

Specifies the first sequence number generated. In an ascending sequence, the value must be greater than or equal to the MINVALUE. In a descending sequence, the value must be greater than or equal to the MAXVALUE. For ascending sequences, the default value is MINVALUE. For descending sequences, the default value is MAXVALUE.

MAXVALUE

Specifies the maximum value for the sequence to generate. For an ascending sequence, the default value is 2,147,483,647. For a descending sequence, the default value is -1.

NOMAXVALUE

Specifies -1 as the MAXVALUE for descending sequences and 2,147,483,647 as the MAXVALUE for ascending sequences.

MINVALUE

Specifies the minimum value the sequence can generate. For an ascending sequence, the default value is 0. For a descending sequence, the default value is -2,147,483,648.

NOMINVALUE

Specifies 0 as the MINVALUE for ascending sequences and -2,147,483,648 as the MINVALUE for descending sequences.

CYLCE

Indicates that the sequence will continue to generate values after reaching the value assigned to MAXVALUE (if sequence ascends) or MINVALUE (if sequence descends).

NOCYCLE

Indicates that the sequence cannot generate more values after reaching the value assigned to MAXVALUE (if sequence ascends) or MINVALUE (if sequence descends). The SQL-92 sequence generator uses NOCYCLE as the default if CYCLE is not specified.

In the following example, a sequence is used to generate unique customer numbers when a new customer is inserted into the table pub.customer:

Example

CREATE SEQUENCE pub.customer_sequence      START WITH 100,      INCREMENT BY 1,      NOCYLCE;